import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.ResultSetMetaData;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.HashMap;

/**
 * main data base provider, allow to execute db functions
 * Usually used as class extensions
 * 
 * @author anton
 *
 */
public abstract class DataBaseAccess {
	private ResultSet tables; 
	private String tableName;
	protected EWDataBase db;
	
	public static int TABLE_DOES_NOT_EXISTS = 404;
	public static int RECORD_EXISTS_IN_TABLE = 303;
	
	/**
	 * create access for tables
	 * @param name	name for table which wish to connect
	 */
	public DataBaseAccess(String name) {
		tableName = name;
		String[] tableTypes = {"TABLE"};
		db = EWDataBase.getInstance();
		try {
			tables = db.conn.getMetaData().getTables(null, null, tableName, tableTypes);
		} catch (SQLException e) {
			e.printStackTrace();
		}
	}
	  
	/**
	 * user procedure to get data set from DB by keys
	 * @param keys - db parameters
	 * @return data set
	 */
	protected abstract ResultSet getListByDetail(HashMap<?, ?> keys); // function return all record which satisfies keys.
	
	/**
	 * Delete one record from table, !!! do not resolving dependency !!!
	 * @param keys	language name in table
	 * @return	if operation execute success function return 0, else function return error code.
	 * 
	 */
	protected int deleteOneRecord(ArrayList<String> keys){ // delete on record from table
		String deleteSQL = "DELETE FROM " + tableName + " WHERE ";
		PreparedStatement stmt;
		try {
			if (tables.getRow() == 0) return TABLE_DOES_NOT_EXISTS;
			for (int i = 0; keys.size() < i; i++){
				if (i > 0) 	deleteSQL = deleteSQL + " AND " + keys.get(i);
				else deleteSQL = deleteSQL + keys.get(i);
			}
			
			stmt = db.conn.prepareStatement(deleteSQL);
			stmt.executeBatch();
	       
			System.out.println("\n record in table "+ tableName +" deleted") ;
			stmt.close() ;
			return 0;

		} catch (SQLException e) {
			e.printStackTrace();
			return e.getErrorCode();
		}
	}
	
	/**
	 * Update records in db
	 * @param whereParams - prepared select params for update record
	 * @param setsParams - prepare params for update
	 * @return - executed result
	 */
	protected int updateOneRecord(ArrayList<String> whereParams, ArrayList<String> setsParams){
		String updateSQL = "UPDATE " + tableName + " SET ";
		for (int i = 0; i < setsParams.size(); i++) {
			if (i>0) updateSQL += ", " + setsParams.get(i);
			else updateSQL += setsParams.get(i);
		};
		updateSQL += " WHERE ";
		for (int i = 0; i < whereParams.size(); i++) {
			if (i>0) updateSQL += " AND " + whereParams.get(i);
			else updateSQL += whereParams.get(i);
		};
		
		try {
			PreparedStatement stmt = db.conn.prepareStatement(updateSQL);
			stmt.execute();
			int res = stmt.getResultSetType();
			stmt.close();
			return res; 
		} catch (SQLException e) {
			e.printStackTrace();
			return -1;
		}
	}
	
	/**
	 * insert records in to db
	 * @param insertValues - parameters for inserts
	 * @return - executed result
	 */
	protected int insertOneRecord(HashMap<String, String> insertValues){ // insert one record in table
		String insertSQL = "INSERT INTO " + tableName + " VALUES (";
		PreparedStatement stmt;
		try { 
			ResultSetMetaData rsmtd = db.conn.prepareStatement("Select * from " + tableName).getMetaData();
			String columnName;
			String columnType;
			for (int i = 0; i < insertValues.size(); i++) {
					columnName = rsmtd.getColumnName(i+1);
					columnType = rsmtd.getColumnTypeName(i+1);
					if (columnType.equals("VARCHAR")) insertSQL += ((i==0)?"'":", '") + insertValues.get(columnName) + "'";
					else {if (columnType.equals("TIMESTAMP")) insertSQL += ((i==0)?"'":", '") + insertValues.get(columnName) + "'";
						else {insertSQL += ((i==0)?"":", ") + insertValues.get(columnName);}
					};			
			}
			insertSQL += ")";
			
			stmt = db.conn.prepareStatement(insertSQL);
			stmt.execute();
			int res = stmt.getResultSetType();
			stmt.close();
			return res; 
		} catch (SQLException e) {
			e.printStackTrace();
			return -1;
		}		
	}
	
	/**
	 * Execute user prepared query 
	 * @param queryToRun - prepared query
	 * @return - data set
	 * @throws SQLException - sql exceptions
	 */
	protected ResultSet executeAnyQuery(String queryToRun) throws SQLException{
		return db.conn.prepareStatement(queryToRun).executeQuery();
	}
}
